package cn.zhl.txt;

import cn.zhl.txt.excel.ValueEo;
import cn.zhl.txt.excel.ValueExcelListener;
import com.alibaba.excel.EasyExcel;
import org.junit.Before;
import org.junit.Test;

import java.io.*;
import java.util.*;

/**
 * @program: demos
 * @description:
 * @author: 刘振华
 * @create: 2020-11-04 11:42
 **/
public class TogetherSql {
	private Map<String,String> map = new LinkedHashMap<>(56);
	private Map<String,String> tnmap = new HashMap<>(60);
	private Map<String,String> transMap = new HashMap<>(5);
	public static Set<String> valSet = new HashSet<>(500);

	@Test
	public void together()throws Exception{
		String fileName = "D:\\work\\文档\\3.塔里木\\设计\\设备数据管理与应用项目模型设计(Oracle).xlsx";
		// 读取全部sheet
		// 这里需要注意 DemoDataListener的doAfterAllAnalysed 会在每个sheet读取完毕后调用一次。然后所有sheet都会往同一个DemoDataListener里面写
		EasyExcel.read(fileName, ValueEo.class, new ValueExcelListener()).doReadAll();

		String rsPath = "D:\\tmp\\rs.csv";
		File file = new File(rsPath);
		file.delete();
		file.createNewFile();
		BufferedWriter bfw = new BufferedWriter(new FileWriter(file));
		for(String key:map.keySet()){

			String cname = tnmap.get(key);
			String bankPath = "C:\\Users\\Administrator\\Desktop\\pdm.csv";
			BufferedReader bankBfr = new BufferedReader(new FileReader(bankPath));
			Map<String, String> bankMap = new LinkedHashMap<>(60);
			String bankLine = "";
			boolean bankFlag = false;
			while ((bankLine = bankBfr.readLine()) != null) {
				if(bankLine.startsWith("表名")) {
					String tableName = bankLine.split(",")[1];
					if (key.equals(tableName)) {
						bankFlag = true;
					}
				}else {
					if (!bankLine.startsWith("字段中文名") && bankFlag) {
						if (",,".equals(bankLine)) {
							bankFlag = false;
							break;
						}
//					if(bankLine.endsWith(")")||bankLine.endsWith("\"")||bankLine.endsWith("DATE")){
						String[] strs = bankLine.split(",");
						String bl = cname + "," + key + "," + bankLine+",";
						if(strs[1].startsWith("PK_")) {
							bl += "Y";
						}
						bankMap.put(strs[1],bl);
//					}
					}
				}
			}
			bankBfr.close();


			String sourthPath = "D:\\work\\文档\\3.塔里木\\数据库\\sql\\业务表.sql";

			BufferedReader sourceBfr = new BufferedReader(new FileReader(sourthPath));
			Map<String,String> sourceMap = new HashMap<>(60);
			String sourceLine = "";
			boolean sourceFlag = false;
			String sourceTabName = map.get(key);
			while ((sourceLine = sourceBfr.readLine())!= null){
				String tableName = "\""+sourceTabName+"\"";
				if(sourceLine.contains(tableName)){
					sourceFlag = true;
				}
				if(sourceFlag){
					if(")".equals(sourceLine)){
						break;
					}
					if(sourceLine.startsWith("  \"")) {
						String[] strs = sourceLine.split("\"");
						String colName = strs[1];
						String type = "\""+strs[2].replaceAll(" ,", "").trim()
							.replaceAll(" NOT NULL", "")
							.replaceAll(" BYTE", "")+"\"";
						String sl = cname + "," + sourceTabName + "," + colName + "," + type+", ";
						if(colName.startsWith("PK_")){
							sl += "Y";
						}
						sourceMap.put(colName, sl);
					}
				}
			}
			sourceBfr.close();

			Set<String> set = new HashSet<>();
			for(String bkey : bankMap.keySet()){
				String nbKey = transMap.get(bkey);
				if(nbKey == null){
					nbKey = bkey;
				}
				if(!sourceMap.containsKey(nbKey)){
					nbKey = bkey;
				}
				String isValue = "直接映射";
				if(valSet.contains(nbKey)){
					isValue = "属性规范值";
				}
				if(sourceMap.containsKey(nbKey)) {
					String sourceVal = sourceMap.get(nbKey);
					String bankVal = bankMap.get(bkey);
//					System.out.println(sourceVal);//锅炉技术特性,CD_BAS_BOILER_INST,PK_INST_ID,VARCHAR2(32)
//					System.out.println(bankVal);//锅炉技术特性,EQ_BAS_BOILER_INST,技术特性ID,PK_INST_ID,VARCHAR2(32)
					String colName = bankVal.split(",")[2];
					String[] split = sourceVal.split(",");
					StringBuilder sb = new StringBuilder();
					for(int i = 0;i<split.length;i++){
						sb.append(split[i]).append(",");
						if(i==1){
							sb.append(colName).append(",");
						}
					}
					bankMap.put(bkey, sb.toString() +",,,,,"+isValue+",,,"+ bankVal);
					set.add(nbKey);
				}
			}
			for(String bk : bankMap.keySet()){
				if(bankMap.get(bk).split(",").length <10){
					bfw.write(",,,,,,,,,,,,,,");
				}
				bfw.write(bankMap.get(bk));
				bfw.newLine();
			}
			for(String l : sourceMap.keySet()){
				if(!set.contains(l)) {
					bfw.write(sourceMap.get(l)+",源多出来的");
					bfw.newLine();
				}
			}
			bfw.flush();
		}
		bfw.close();
	}

	@Before
	public void init(){
		transMap.put("EQUIP_ID","FK_EQU_ID");
		transMap.put("UPDATE_USER_ID","CREATOR");
		transMap.put("REMARKS","REMARK");
		transMap.put("CREATE_DATE","CREATE_TIME");
		map.put("CD_EQUIP","CD_BAS_EQU");
		map.put("EQ_BAS_BOILER_OTHER","CD_BAS_BOILER_OTHER");
		map.put("EQ_BAS_BOM_MANAGE","CD_BAS_BOM_MANAGE");
		map.put("EQ_BAS_BURNER","CD_BAS_BURNER");
		map.put("EQ_BAS_CREEP_POINT","CD_BAS_CREEP_POINT");
		map.put("EQ_BAS_CYLINDER","CD_BAS_CYLINDER");
		map.put("EQ_BAS_CYLINDER_INST","CD_BAS_CYLINDER_INST");
		map.put("EQ_BAS_CYLINDER_NORM","CD_BAS_CYLINDER_NORM");
		map.put("EQ_BAS_DATA_FILE","CD_BAS_DATA_FILE");
		map.put("EQ_DES_DES_MAKE","CD_BAS_DES_MAKE");
		map.put("CD_BAS_DOC_MANAGE","EQ_BAS_DOC_MANAGE");
		map.put("EQ_BAS_ELEC_EQU","CD_BAS_ELEC_EQU");
		map.put("EQ_BAS_ELEC_INST","CD_BAS_ELEC_INST");
		map.put("EQ_BAS_ELEC_OTHER","CD_BAS_ELEC_OTHER");
		map.put("EQ_BAS_EQU_ATTR_ADMIN","CD_BAS_EQU_ATTR_ADMIN");
		map.put("EQ_BAS_EQU_ATTR_INST","CD_BAS_EQU_ATTR_INST");
		map.put("EQ_BAS_EQU_BOM","CD_BAS_EQU_BOM");
		map.put("EQ_BAS_EQU_RECORD","CD_BAS_EQU_RECORD");
		map.put("EQ_BAS_EQU_SORT","CD_BAS_EQU_SORT");
		map.put("EQ_BAS_EQU_SORT_INST","CD_BAS_EQU_SORT_INST");
		map.put("EQ_BAS_FAULT_CASE","CD_BAS_FAULT_CASE");
		map.put("EQ_BAS_FAULT_CODE","CD_BAS_FAULT_CODE");
		map.put("EQ_BAS_HEAD_NORM","CD_BAS_HEAD_NORM");
		map.put("EQ_BAS_HEADER_SPECS","CD_BAS_HEADER_SPECS");
		map.put("EQ_BAS_HEAT_ANTISEPTIC","CD_BAS_HEAT_ANTISEPTIC");
		map.put("EQ_BAS_HEATER","CD_BAS_HEATER");
		map.put("EQ_BAS_HEATER_INST","CD_BAS_HEATER_INST");
		map.put("EQ_BAS_INHIBITOR","CD_BAS_INHIBITOR");
		map.put("EQ_BAS_INSTALL","CD_BAS_INSTALL");
		map.put("EQ_BAS_MACHINE_PERFORM","CD_BAS_MACHINE_PERFORM");
		map.put("EQ_BAS_PIPE_FITTING","CD_BAS_PIPE_FITTING");
		map.put("EQ_BAS_PIPELINE","CD_BAS_PIPELINE");
		map.put("EQ_BAS_POINT_UWUTM","CD_BAS_POINT_UWUTM");
		map.put("CD_BAS_RESPIRATOR","CD_BAS_RESPIRATOR");
		map.put("CD_BAS_RULE_MANAGE","CD_BAS_RULE_MANAGE");
		map.put("EQ_BAS_SAFE_ANNEX","CD_BAS_SAFE_ANNEX");
		map.put("EQ_BAS_SAFE_PART","CD_BAS_SAFE_FILE");
		map.put("EQ_BAS_SAFE_INST","CD_BAS_SAFE_INST");
		map.put("EQ_BAS_SEAL_POINT","CD_BAS_SEAL_POINT");
		map.put("EQ_BAS_SOCKET_NORM","CD_BAS_SOCKET_NORM");
		map.put("EQ_BAS_SOP_MANAGE","CD_BAS_SOP_MANAGE");
		map.put("EQ_BAS_SPECS_MATERIAL","CD_BAS_SPECS_MATERIAL");
		map.put("EQ_BAS_SUP_MANAGE","CD_BAS_SUP_MANAGE");
		map.put("EQ_BAS_SURFACE_TUBE","CD_BAS_SURFACE_TUBE");
		map.put("EQ_BAS_TEST_INFO","CD_BAS_TEST_INFO");
		map.put("EQ_BAS_VESSEL","CD_BAS_VESSEL");
		map.put("EQ_BAS_VESSEL_INST","CD_BAS_VESSEL_INST");
		map.put("EQ_BAS_VESSEL_OTHER","CD_BAS_VESSEL_OTHER");
		map.put("EQ_BAS_VESSEL_PART","CD_BAS_VESSEL_PART");
		map.put("EQ_BAS_VESSEL_PIPELINE","CD_BAS_VESSEL_PIPELINE");
		map.put("EQ_BAS_VOLTAGE_TEST","CD_BAS_VOLTAGE_TEST");
		map.put("EQ_BAS_WELD_LINE","CD_BAS_WELD_LINE");
		map.put("EQ_BAS_LINE_MANG","EQ_BAS_LINE_MANG");
		map.put("EQ_BAS_MAINTAIN_CARD","EQ_BAS_MAINTAIN_CARD");
		map.put("EQ_BAS_SPECIAL_TOOL","EQ_BAS_SPECIAL_TOOL");
		map.put("EQ_BAS_TECH_SPEC","EQ_BAS_TECH_SPEC");

		tnmap.put("EQ_BAS_BOILER_INST","锅炉技术特性");
		tnmap.put("EQ_BAS_BOILER_OTHER","锅炉其他信息");
		tnmap.put("EQ_BAS_BOM_MANAGE","BOM");
		tnmap.put("EQ_BAS_BURNER","燃烧器");
		tnmap.put("EQ_BAS_CREEP_POINT","管道蠕变测点");
		tnmap.put("EQ_BAS_CYLINDER","气瓶基本信息");
		tnmap.put("EQ_BAS_CYLINDER_INST","气瓶技术特性");
		tnmap.put("EQ_BAS_CYLINDER_NORM","圆筒规范");
		tnmap.put("EQ_BAS_DATA_FILE","资料文件");
		tnmap.put("EQ_DES_DES_MAKE","设计及制造信息");
		tnmap.put("EQ_BAS_DOC_MANAGE","文档管理");
		tnmap.put("EQ_BAS_ELEC_EQU","机电类特种设备基本信息");
		tnmap.put("EQ_BAS_ELEC_INST","机电类特种设备技术特性");
		tnmap.put("EQ_BAS_ELEC_OTHER","机电类特种设备其他信息");
		tnmap.put("EQ_BAS_EQU_ATTR_ADMIN","设备特性数据");
		tnmap.put("EQ_BAS_EQU_ATTR_INST","设备特性实例");
		tnmap.put("EQ_BAS_EQU_BOM","设备BOM");
		tnmap.put("EQ_BAS_EQU_RECORD","设备档案资料");
		tnmap.put("EQ_BAS_EQU_SORT","设备分类");
		tnmap.put("EQ_BAS_EQU_SORT_INST","设备分类与特性关系表");
		tnmap.put("EQ_BAS_FAULT_CASE","故障案例库");
		tnmap.put("EQ_BAS_FAULT_CODE","故障代码");
		tnmap.put("EQ_BAS_HEAD_NORM","封头规范");
		tnmap.put("EQ_BAS_HEADER_SPECS","联箱规格材质");
		tnmap.put("EQ_BAS_HEAT_ANTISEPTIC","保温防腐信息");
		tnmap.put("EQ_BAS_HEATER","加热炉基本信息");
		tnmap.put("EQ_BAS_HEATER_INST","加热炉技术特性");
		tnmap.put("EQ_BAS_INHIBITOR","管道缓蚀剂");
		tnmap.put("EQ_BAS_INSTALL","管道安装信息");
		tnmap.put("EQ_BAS_MACHINE_PERFORM","机械性能");
		tnmap.put("EQ_BAS_PIPE_FITTING","管道管件");
		tnmap.put("EQ_BAS_PIPELINE","管道基本信息");
		tnmap.put("EQ_BAS_POINT_UWUTM","管道定点测厚");
		tnmap.put("CD_BAS_RESPIRATOR","呼吸器基本信息");
		tnmap.put("CD_BAS_RULE_MANAGE","规章制度管理模型");
		tnmap.put("EQ_BAS_SAFE_ANNEX","安全附件基本信息");
		tnmap.put("EQ_BAS_SAFE_PART","安全附件");
		tnmap.put("EQ_BAS_SAFE_INST","安全附件技术特性");
		tnmap.put("EQ_BAS_SEAL_POINT","管道密封点");
		tnmap.put("EQ_BAS_SOCKET_NORM","接管座规范");
		tnmap.put("EQ_BAS_SOP_MANAGE","标准规范");
		tnmap.put("EQ_BAS_SPECS_MATERIAL","管道规格材质");
		tnmap.put("EQ_BAS_SUP_MANAGE","设备供应商");
		tnmap.put("EQ_BAS_SURFACE_TUBE","受热面及炉管规范");
		tnmap.put("EQ_BAS_TEST_INFO","检验信息");
		tnmap.put("EQ_BAS_VESSEL","容器基本信息");
		tnmap.put("EQ_BAS_VESSEL_INST","容器技术特性");
		tnmap.put("EQ_BAS_VESSEL_OTHER","容器其他信息");
		tnmap.put("EQ_BAS_VESSEL_PART","容器零件信息");
		tnmap.put("EQ_BAS_VESSEL_PIPELINE","容器接管信息");
		tnmap.put("EQ_BAS_VOLTAGE_TEST","管道耐压试验");
		tnmap.put("EQ_BAS_WELD_LINE","管道焊缝");
		tnmap.put("EQ_BAS_LINE_MANG","电气线路管理");
		tnmap.put("EQ_BAS_MAINTAIN_CARD","维修保养卡");
		tnmap.put("EQ_BAS_SPECIAL_TOOL","附带专用工具");
		tnmap.put("EQ_BAS_TECH_SPEC","技术规范");
		tnmap.put("CD_EQUIP","设备");

	}

}
